package org.clipjoint.dao.impl;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.clipjoint.dao.UserDao;
import org.clipjoint.entity.Purchased;
import org.clipjoint.entity.User;
import org.clipjoint.entity.Dream;
import org.clipjoint.util.DataSourceUtil;

import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {


    // 插入一条user数据
    public int insert(User user) throws SQLException {

        // 创建DButils核心对象
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        // 编写sql语句
        String sql = "insert into `user`(`email`, `username`, `password`, `create_time`, `account`, `credit`, `image`) value(?, ?, ?, ?, ?, ?, ?)";

        // 执行sql
        int result = qr.update(sql, user.getEmail(), user.getEmail(), user.getPassword(), user.getCreate_time(), user.getAccount(), user.getCredit(), user.getImage());

        return result;
    }


    //查看邮箱唯一性
    public User selectByEmail(User user) throws SQLException {
        // 创建DButils核心对象
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        // 编写sql语句
        String sql = "select * from `user` where `email` = ?";

        // 执行sql
        User result = qr.query(sql, new BeanHandler<>(User.class), user.getEmail());

        return result;
    }

    //用户登录
    @Override
    public User selectByEmailAndPassword(User user) throws SQLException {
        // 创建DButils核心对象
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        // 编写sql语句
        String sql = "select * from `user` where `email` = ? and `password` = ? ";

        // 执行sql
        User result = qr.query(sql, new BeanHandler<>(User.class), user.getEmail(), user.getPassword());

        return result;
    }

    //更新用户数据
    @Override
    public int update(User user) throws SQLException{

        // 创建DButils核心对象
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        // 编写sql语句
        String sql = "update `user` set `email` = ?, `username` = ?, `password` = ?, `image` = ?, `last_login_time` = ?, `account` = ?, `credit` = ? where `id` = ?";

        // 执行sql
        int result = qr.update(sql, user.getEmail(), user.getUsername(), user.getPassword(), user.getImage(), user.getLast_login_time(), user.getAccount(), user.getCredit(), user.getId());

        return result;
    }

    @Override
    public List<Dream> dream(Long id) throws SQLException {
        // 创建DButils核心对象
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        // 编写sql语句
        String sql = "select * from `dream` where `user_id` = ?";

        // 执行sql
        List<Dream> result = qr.query(sql, new BeanListHandler<>(Dream.class),id);

        return result;
    }

    @Override
    public List<Purchased> purchased(Long id) throws SQLException {
        // 创建DButils核心对象
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        // 编写sql语句
        String sql = "select * from `purchased` where `user_id` = ?";

        // 执行sql
        List<Purchased> result = qr.query(sql, new BeanListHandler<>(Purchased.class),id);

        return result;
    }



}
